/*
	exec sp_get_board_kit_parts_by_ps_type '0','0'
*/
CREATE PROC sp_get_board_kit_parts_by_ps_type

@ps_type	varchar(50),
@boardorkit	varchar(50)

as
begin

	declare @results table ( part_number varchar(50), processstep_id varchar(50) )

	if ( @ps_type is not null and @ps_type <> '0' )
	begin
		if ( @boardorkit = '0' or @boardorkit = 'board' )
		begin
			insert into @results values('----BOARD------','-1')
		
			insert into @results
			select part_number,p.processstep_id from product p, process_step ps where p.processstep_id = ps.processstep_id and p_type = 'board' and ps_type = @ps_type order by part_number 
		end
		
		if ( @boardorkit = '0' or @boardorkit = 'kit' )
		begin
			insert into @results values('----KIT------','-2')
		
			insert into @results
			select part_number,p.processstep_id from product p, process_step ps where p.processstep_id = ps.processstep_id and p_type = 'kit' and ps_type = @ps_type order by part_number
		end		
	end
	else
	begin
		if ( @boardorkit = '0' or @boardorkit = 'board' )
		begin
			insert into @results values('----BOARD------','-1')
		
			insert into @results
			select distinct part_number,part_number from product where p_type = 'board' order by part_number 
		end
		
		if ( @boardorkit = '0' or @boardorkit = 'kit' )
		begin
			insert into @results values('----KIT------','-2')
		
			insert into @results
			select part_number,part_number from product  where p_type = 'kit' order by part_number
		end
	end

	select part_number,processstep_id from @results 
end